package cn.kain.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.kain.dao.StaffDao;
import cn.kain.entity.Staff;
import cn.kain.util.DBTool;

/**
 * @author kai
 * @email kain.wong@foxmail.com
 * @Data 2020年1月3日 下午11:45:03
 * @Description TODO
 * 
 */
public class StaffDaoImpl implements StaffDao {

	private Connection conn = null;
	private PreparedStatement psmt = null;
	
	@Override
	public List<Staff> getAllStaff() {
		String sql = "SELECT * FROM staff";
		List<Staff> staffList = new ArrayList<Staff>();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while(rs.next()) {
				Staff staff = new Staff();
				staff.setId(rs.getInt("id"));
				staff.setLoginNum(rs.getString("login_num"));
				staff.setLoginPwd(rs.getString("login_pwd"));
				staff.setName(rs.getString("name"));
				staff.setAge(rs.getInt("age"));
				staff.setSex(rs.getString("sex"));
				staff.setPhone(rs.getString("phone"));
				staff.setIdcard(rs.getString("idcard"));
				staff.setEntry(rs.getString("entry"));
				staff.setNote(rs.getString("note"));
				staffList.add(staff);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return staffList;
	}

	@Override
	public Staff login(String num, String pwd) {
		String sql = "SELECT * FROM staff WHERE login_num=? and login_pwd=?";
		Staff staff = new Staff();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, num);
			psmt.setString(2, pwd);
			ResultSet rs = psmt.executeQuery();
			if(rs.next()) {
				staff.setId(rs.getInt("id"));
				staff.setLoginNum(num);
				staff.setLoginPwd(pwd);
				staff.setName(rs.getString("name"));
				staff.setAge(rs.getInt("age"));
				staff.setSex(rs.getString("sex"));
				staff.setPhone(rs.getString("phone"));
				staff.setIdcard(rs.getString("idcard"));
				staff.setEntry(rs.getString("entry"));
				staff.setNote(rs.getString("note"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBTool.close(conn);
		}
		return staff;
	}

	@Override
	public Staff selectById(int id) {
		String sql = "SELECT * FROM staff WHERE id =?";
		Staff staff = new Staff();
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs = psmt.executeQuery();
			if(rs.next()) {
				staff.setId(rs.getInt("id"));
				staff.setLoginNum(rs.getString("login_num"));
				staff.setLoginPwd(rs.getString("login_pwd"));
				staff.setName(rs.getString("name"));
				staff.setAge(rs.getInt("age"));
				staff.setSex(rs.getString("sex"));
				staff.setPhone(rs.getString("phone"));
				staff.setIdcard(rs.getString("idcard"));
				staff.setEntry(rs.getString("entry"));
				staff.setNote(rs.getString("note"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return staff;
	}

	@Override
	public void addStaff(Staff s) {
		String sql = "INSERT INTO staff "
				+ "(login_num,login_pwd,name,age,sex,phone,idcard,entry,note) values (?,?,?,?,?,?,?,?,?)";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, s.getLoginNum());
			psmt.setString(2, s.getLoginPwd());
			psmt.setString(3, s.getName());
			psmt.setInt(4, s.getAge());
			psmt.setString(5, s.getSex());
			psmt.setString(6, s.getPhone());
			psmt.setString(7, s.getIdcard());
			psmt.setString(8, s.getEntry());
			psmt.setString(9, s.getNote());
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void deleteStaff(int id) {
		String sql = "DELETE FROM staff WHERE id=?";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateStaff(Staff s) {
		String sql = "UPDATE staff set "
				+ "login_num=?,login_pwd=?,name=?,age=?,sex=?,phone=?,idcard=?,entry=?,note=? "
				+ "WHERE id=? ";
		try {
			conn = DBTool.getConnection();
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, s.getLoginNum());
			psmt.setString(2, s.getLoginPwd());
			psmt.setString(3, s.getName());
			psmt.setInt(4, s.getAge());
			psmt.setString(5, s.getSex());
			psmt.setString(6, s.getPhone());
			psmt.setString(7, s.getIdcard());
			psmt.setString(8, s.getEntry());
			psmt.setString(9, s.getNote());
			psmt.setInt(10, s.getId());
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
